﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using Gem_Tools_DataLayer;
using Gem_Tools_Utility;
using Gem_Tools_Entity.Gem_Tools;

namespace Gem_Tools_Controller.Gem_Tools
{
    /// <summary>
    /// 28-10-2013: cập nhật hàm Login dành cho Staff.
    /// </summary>
    public class CStaff_Controller
    {
        private void MapStaff(DataRow p_row, CStaff p_objDes)
        {
            DataTable p_dt = p_row.Table;

            if (p_dt.Columns.Contains("Auto_ID"))
                p_objDes.Auto_ID = CUtility.Convert_To_Int32(p_row["Auto_ID"]);

            if (p_dt.Columns.Contains("UserName"))
                p_objDes.UserName = CUtility.Convert_To_String(p_row["UserName"]);

            if (p_dt.Columns.Contains("Password"))
                p_objDes.Password = CUtility.Convert_To_String(p_row["Password"]);

            if (p_dt.Columns.Contains("Full_Name"))
                p_objDes.Full_Name = CUtility.Convert_To_String(p_row["Full_Name"]);

            if (p_dt.Columns.Contains("Regency_ID"))
                p_objDes.Regency_ID = CUtility.Convert_To_Int32(p_row["Regency_ID"]);

            if (p_dt.Columns.Contains("Email"))
                p_objDes.Email = CUtility.Convert_To_String(p_row["Email"]);

            if (p_dt.Columns.Contains("Extension"))
                p_objDes.Extension = CUtility.Convert_To_String(p_row["Extension"]);

            if (p_dt.Columns.Contains("Note"))
                p_objDes.Note = CUtility.Convert_To_String(p_row["Note"]);

            if (p_dt.Columns.Contains("is_Active"))
                p_objDes.is_Active = CUtility.Convert_To_Int32(p_row["is_Active"]);

            if (p_dt.Columns.Contains("Created"))
                p_objDes.Created = CUtility.Convert_To_DateTime(p_row["Created"]);

            if (p_dt.Columns.Contains("Created_By"))
                p_objDes.Created_By = CUtility.Convert_To_String(p_row["Created_By"]);

            if (p_dt.Columns.Contains("Deleted"))
                p_objDes.Deleted = CUtility.Convert_To_Int32(p_row["Deleted"]);

            if (p_dt.Columns.Contains("Last_Updated"))
                p_objDes.Last_Updated = CUtility.Convert_To_DateTime(p_row["Last_Updated"]);

            if (p_dt.Columns.Contains("Last_Updated_By"))
                p_objDes.Last_Updated_By = CUtility.Convert_To_String(p_row["Last_Updated_By"]);

            if (p_dt.Columns.Contains("Current_Project"))
                p_objDes.Current_Project = CUtility.Convert_To_String(p_row["Current_Project"]);

        }

        public IList<CStaff> List_Staff()
        {
            IList<CStaff> v_arrStaff = new List<CStaff>();
            DataTable v_dt = new DataTable();

            try
            {
                CSqlHelper.FillDataTable(CConfig.g_strGem_Tools_Conn_String, v_dt, "sp_sel_List_Staff");

                foreach (DataRow v_row in v_dt.Rows)
                {
                    CStaff v_objStaff = new CStaff();
                    MapStaff(v_row, v_objStaff);
                    v_arrStaff.Add(v_objStaff);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                v_dt.Dispose();
            }

            return v_arrStaff;
        }

        public IList<CStaff> A0019_sp_sel_List_Staff_Not_In_Project_Staff_By_Project_ID(int p_iProject_ID, int p_iRegency_ID)
        {
            IList<CStaff> v_arrStaff = new List<CStaff>();
            DataTable v_dt = new DataTable();

            try
            {
                CSqlHelper.FillDataTable(CConfig.g_strGem_Tools_Conn_String, v_dt, "A0019_sp_sel_List_Staff_Not_In_Project_Staff_By_Project_ID", p_iProject_ID, p_iRegency_ID);

                foreach (DataRow v_row in v_dt.Rows)
                {
                    CStaff v_objStaff = new CStaff();
                    MapStaff(v_row, v_objStaff);
                    v_arrStaff.Add(v_objStaff);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                v_dt.Dispose();
            }

            return v_arrStaff;
        }

        public CStaff Get_Staff_By_ID(int p_iID)
        {
            CStaff v_objStaff = null;
            DataTable v_dt = new DataTable();

            try
            {
                CSqlHelper.FillDataTable(CConfig.g_strGem_Tools_Conn_String, v_dt, "sp_sel_Get_Staff_By_ID", p_iID);

                if (v_dt.Rows.Count > 0)
                {
                    v_objStaff = new CStaff();
                    MapStaff(v_dt.Rows[0], v_objStaff);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                v_dt.Dispose();
            }

            return v_objStaff;
        }

        public int Insert_Staff(CStaff p_objData)
        {
            int v_iRes = CConst.INT_VALUE_NULL;

            try
            {
                v_iRes = Convert.ToInt32(CSqlHelper.ExecuteScalar(CConfig.g_strGem_Tools_Conn_String, "sp_ins_Staff",
                            p_objData.UserName, p_objData.Password, p_objData.Full_Name, p_objData.Regency_ID, p_objData.Email, p_objData.Extension, p_objData.Note, p_objData.is_Active, p_objData.Last_Updated_By).ToString());

            }
            catch (Exception ex)
            {
                throw ex;
            }


            return v_iRes;
        }

        public void Update_Staff(CStaff p_objData)
        {
            try
            {
                CSqlHelper.ExecuteNonquery(CConfig.g_strGem_Tools_Conn_String, "sp_upd_Staff",
                            p_objData.Auto_ID, p_objData.UserName, p_objData.Password, p_objData.Full_Name, p_objData.Regency_ID, p_objData.Email, p_objData.Extension, p_objData.Note, p_objData.is_Active, p_objData.Last_Updated_By);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete_Staff(int p_iID, string p_strLast_Updated_User)
        {
            try
            {
                CSqlHelper.ExecuteNonquery(CConfig.g_strGem_Tools_Conn_String, "sp_del_Staff", p_iID, p_strLast_Updated_User);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public CStaff F0_Login(string username, string password)
        {
            CStaff v_objStaff = null;
            DataTable v_dt = new DataTable();

            try
            {
                CSqlHelper.FillDataTable(CConfig.g_strGem_Tools_Conn_String, v_dt, "0_sp_Login", username, password);

                if (v_dt.Rows.Count > 0)
                {
                    v_objStaff = new CStaff();
                    MapStaff(v_dt.Rows[0], v_objStaff);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                v_dt.Dispose();
            }

            return v_objStaff;
        }

        public IList<CStaff> A003_List_Staff_By_Regency_ID(int piRegency_ID)
        {
            IList<CStaff> v_arrStaff = new List<CStaff>();
            DataTable v_dt = new DataTable();

            try
            {
                CSqlHelper.FillDataTable(CConfig.g_strGem_Tools_Conn_String, v_dt, "A003_sp_sel_List_Staff_By_Regency_ID", piRegency_ID);

                foreach (DataRow v_row in v_dt.Rows)
                {
                    CStaff v_objStaff = new CStaff();
                    MapStaff(v_row, v_objStaff);
                    v_arrStaff.Add(v_objStaff);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                v_dt.Dispose();
            }

            return v_arrStaff;
        }

        public IList<CStaff> A001_sp_sel_List_Producer_Technician()
        {
            IList<CStaff> v_arrStaff = new List<CStaff>();
            DataTable v_dt = new DataTable();

            try
            {
                CSqlHelper.FillDataTable(CConfig.g_strGem_Tools_Conn_String, v_dt, "A001_sp_sel_List_Producer_Technician");

                foreach (DataRow v_row in v_dt.Rows)
                {
                    CStaff v_objStaff = new CStaff();
                    MapStaff(v_row, v_objStaff);
                    v_arrStaff.Add(v_objStaff);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                v_dt.Dispose();
            }

            return v_arrStaff;
        }

        public IList<CStaff> A001_sp_sel_List_Staff_By_Regency_ID(int p_iRegency_ID)
        {
            IList<CStaff> v_arrStaff = new List<CStaff>();
            DataTable v_dt = new DataTable();

            try
            {
                CSqlHelper.FillDataTable(CConfig.g_strGem_Tools_Conn_String, v_dt, "A001_sp_sel_List_Staff_By_Regency_ID", p_iRegency_ID);

                foreach (DataRow v_row in v_dt.Rows)
                {
                    CStaff v_objStaff = new CStaff();
                    MapStaff(v_row, v_objStaff);
                    v_arrStaff.Add(v_objStaff);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                v_dt.Dispose();
            }

            return v_arrStaff;
        }

        public IList<CStaff> A001_sp_sel_List_Staff_By_Regency_And_Project_ID(int p_iProject_ID)
        {
            IList<CStaff> v_arrStaff = new List<CStaff>();
            DataTable v_dt = new DataTable();

            try
            {
                CSqlHelper.FillDataTable(CConfig.g_strGem_Tools_Conn_String, v_dt, "A001_sp_sel_List_Staff_By_Regency_And_Project_ID", p_iProject_ID);

                foreach (DataRow v_row in v_dt.Rows)
                {
                    CStaff v_objStaff = new CStaff();
                    MapStaff(v_row, v_objStaff);
                    v_arrStaff.Add(v_objStaff);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                v_dt.Dispose();
            }

            return v_arrStaff;
        }

    }
}
